package org.fao.pims.persistence;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.ListIterator;

import javax.sql.DataSource;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.fao.pims.model.Document;
import org.fao.pims.model.Taxon;

public class TaxonDAOImpl extends BaseDao implements TaxonDAO {
	
	
	private static Log log = LogFactory.getLog(TaxonDAOImpl.class);
	
	private Connection conn;

	/*
	 * Get the scientific name of crops
	 */
	
	public Taxon getTaxonByScientificName(String name) throws DataAccessException {
		
		Taxon taxon = null;
		
		Statement stmt = null;
        ResultSet rset = null;
        List taxonList = null;
        
        try {
            if( conn == null )
            	conn = this.getDatasource().getConnection();
            
            String query =
            	"SELECT id, scientific_name, common_name FROM (" +
            	"  SELECT t.id, t.lsid, t.scientific_name, v.name as common_name" +            
            	"    FROM taxon_name as t, vernacular as v, taxon_name_vernacular as r" +
            	"   WHERE t.scientific_name = '" + name + "'" + 
            	"     AND r.taxon_name = t.id" +
            	"     AND r.vernacular = v.id) a";

            System.out.println(query);
            
            stmt = conn.createStatement();
            rset = stmt.executeQuery(query);                       
            taxonList = makeBeanList(resultsetToList(rset));
            if( taxonList.size() == 0 ) 
            	throw new DataAccessException();
            taxon = (Taxon) taxonList.get(0);
            rset.close();
            stmt.close();
            
            log.info("Taxon present in the database");
            
        } 
        catch(SQLException e) {
            e.printStackTrace();
            log.error("Cannot get data from the database");
        }
        finally {                        
            
            try 
            { 
                
                    conn.close(); 
            }
            catch(Exception e) { 

            	e.printStackTrace();
                log.error("Cannot close the prepared statement.");
            }
            
        }
        
		return taxon;
		
	}
	
	public List getTaxonByName(String name) throws DataAccessException {
		
		PreparedStatement pstmt = null;
        ResultSet rset = null;
        Connection conn = null;
        List result = new ArrayList();
      
        try {
            
        	conn = this.getDatasource().getConnection();        
            
            String query = 
            	"SELECT id, scientific_name, common_name FROM (" +
            	"  SELECT  t.id, t.scientific_name, v.name AS common_name" + 
            	"    FROM taxon_name as t, vernacular as v, taxon_name_vernacular as r" + 
            	"   WHERE t.id = r.taxon_name" + 
            	"     AND v.id = r.vernacular" +
            	"     AND ( t.scientific_name LIKE '" + name + "%' OR " +
            	"           v.name like '" + name + "%')) a";

            System.out.println(query);
            
            pstmt = conn.prepareStatement(query);
            rset = pstmt.executeQuery();    
            result = makeBeanList(resultsetToList(rset));
            rset.close();
            pstmt.close();

            if( result.size() == 0 )
            	throw new DataAccessException();

            ListIterator rowIterator = result.listIterator();
            while(rowIterator.hasNext())
            {
                Taxon tax = (Taxon) rowIterator.next();
                tax.combineNames();            
            }
            
        } 
        catch(SQLException e) {
            e.printStackTrace();
        }
        finally {
            
            try 
            { 
                if( ! conn.isClosed() )
                    conn.close(); 
                System.out.println("Connection closed");
            }
            catch(Exception e) {
                
                e.printStackTrace();
            }
            
        }
		
		return result;
	}
	
	public List getPollinatorsOfCrop(Taxon crop) {
		
		PreparedStatement pstmt = null;
        ResultSet rset = null;
        Connection conn = null;
        List result = new ArrayList();
      
        try {
        	conn = this.getDatasource().getConnection();         

        	String query = 
        		"SELECT pol.id, pol.lsid, pol.scientific_name, pol.rank, pol.higher_taxon" + 
        		"  FROM taxon_name crp, crop_pollinator cxp, taxon_name pol" +
        		" WHERE crp.id = " + crop.getId() +
        		"   AND crp.id = cxp.crop_taxon_name" +
        		"   AND pol.id = cxp.pollinator_taxon_name";
        	
            pstmt = conn.prepareStatement(query);
            rset = pstmt.executeQuery();    
            result = makeBeanList(resultsetToList(rset));
            
            rset.close();
            pstmt.close();
            conn.close();
            
        } 
        catch(SQLException e) {
            e.printStackTrace();
        }
        finally {
            try 
            { 
                if( ! conn.isClosed() )
                    conn.close(); 
                System.out.println("Connection closed");
            }
            catch(Exception e) {
                e.printStackTrace();
            }
        }
		
		return result;
	}


	public boolean checkHigherTaxon(Taxon taxon) {
		
		boolean isTaxon = false;
		
		Statement stmt = null;
        ResultSet rset = null;
        List taxonList = null;
        
        try {
            if( conn == null )
            	conn = this.getDatasource().getConnection();
            
            String query = "SELECT scientific_name FROM taxon " +
            				"WHERE scientific_name='" + taxon.getHigherTaxon() + "'";
            
            stmt = conn.createStatement();
            rset = stmt.executeQuery(query);                       
            taxonList = makeBeanList(resultsetToList(rset));
            
            if( taxonList.size() > 0 ) {
            	isTaxon = true;
            }
            log.info("Higher taxon present in the database");
        } 
        catch(SQLException e) {
            e.printStackTrace();
            log.error("Cannot get data from the database");
        }
        finally {
            try 
            { 
                if( rset != null )
                    rset.close(); 
            }
            catch(Exception e) {
                e.printStackTrace();
                log.error("Cannot close the result set");
            }
            try 
            { 
                if( stmt != null )
                    stmt.close(); 
            }
            catch(Exception e) { 
                e.printStackTrace();
                log.error("Cannot close the prepared statement.");
            }
        }
		return isTaxon;
	}
	
	public boolean enterTaxon(Taxon taxon) {
		
		boolean result = false;
		
		PreparedStatement pstmt = null;
        ResultSet rset = null;
        
        
        try {
            if( conn == null )
            	conn = this.getDatasource().getConnection();

            
            String query = "insert into taxon set " +
            									"scientific_name = ?, " + 
            									"common_name = ?, " +
            									"name_status = ?, " +
            									"rank = ?, " +
            									"higher_taxon = ?, " +
            									"classification = ?, " +
            									"author = ?, " +
            									"year = ?, " +
            									"lsid = ?";
            								
            
            pstmt = conn.prepareStatement(query);
            
            pstmt.setString(1, taxon.getScientificName());
          
            pstmt.setString(2, taxon.getCommonName());
            
            pstmt.setString(3, taxon.getNameStatus());
            
            pstmt.setString(4, taxon.getRank());
            
            pstmt.setString(5, taxon.getHigherTaxon());
            
            pstmt.setString(6, taxon.getClassification());
            
            pstmt.setString(7, taxon.getAuthor());
            
            pstmt.setString(8, taxon.getYear());
            
            pstmt.setString(9, taxon.getLsid());
            
            
            result = pstmt.execute();
                                  
            log.info("Saved taxonomic information into the database.");
            
            rset.close();
            
            pstmt.close();
            
            
            
        } 
        catch(SQLException e) {
            e.printStackTrace();
            log.error("Cannot get data from the database");
        }
        finally {
            
            try 
            { 
                if( conn != null )
                    conn.close(); 
                
            }
            catch(Exception e) {
                
                e.printStackTrace();
                
            }
            
            
            
        }

		
		return result;
	}
	
	/**
     * Preleva i dati del result set contenuti in un oggetto List e li salva in un 
     * bean appropriato, facendo corrispondere un bean a ciascun record.
     * Restituisce una lista di bean
     */
    protected List makeBeanList(List rows)
    {
        List beanList = new ArrayList();
         
        ListIterator rowIterator = rows.listIterator();
        int rowcounter = 0;
        while(rowIterator.hasNext())
        {
            rowcounter++;
            List fieldlist = (List)rowIterator.next();
            ListIterator fieldsIterator = fieldlist.listIterator();
            Object obj = null;
            String type = "";
            String columnName = "";
            Taxon bean = new Taxon();
            
            
            while(fieldsIterator.hasNext())
            {
                TableFieldHandler tfhandler = (TableFieldHandler)fieldsIterator.next();
                obj = tfhandler.getObject();
                type = tfhandler.getType();
                columnName = tfhandler.getColumnName().trim();
                // System.out.println(columnName);
                if( columnName.equalsIgnoreCase("ID") )
                {
                    bean.setId( ((Integer)obj).intValue() );
                    //System.out.println("scientific name: " + bean.getScientificName());
                }                
                if( columnName.equalsIgnoreCase("SCIENTIFIC_NAME") )
                {
                    bean.setScientificName( (String)obj );
                    //System.out.println("scientific name: " + bean.getScientificName());
                }
                if( columnName.equalsIgnoreCase("COMMON_NAME") )
                {
                    bean.setCommonName( (String)obj );
                    //System.out.println("common name: " + bean.getCommonName());
                }
                if( columnName.equalsIgnoreCase("NAME_STATUS") )
                {
                    bean.setNameStatus( (String)obj );
                    //System.out.println("common name: " + bean.getCommonName());
                }
                if( columnName.equalsIgnoreCase("RANK") )
                {
                    bean.setRank( (String)obj );
                    //System.out.println("common name: " + bean.getCommonName());
                }
                if( columnName.equalsIgnoreCase("HIGHER_TAXON") )
                {
                    bean.setHigherTaxon( (String)obj );
                    //System.out.println("common name: " + bean.getCommonName());
                }
                if( columnName.equalsIgnoreCase("AUTHOR") )
                {
                    bean.setAuthor( (String)obj );
                    //System.out.println("common name: " + bean.getCommonName());
                }
                if( columnName.equalsIgnoreCase("YEAR") )
                {
                    bean.setYear( (String)obj );
                    //System.out.println("common name: " + bean.getCommonName());
                }
                if( columnName.equalsIgnoreCase("LSID") )
                {
                    bean.setLsid( (String)obj );
                    //System.out.println("common name: " + bean.getCommonName());
                }
                         
            }
            
            beanList.add(bean);
            
         }
        
        return beanList;
        
    }


}
